﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using LinqToExcel;
using System.Configuration;
using System.Reflection;
using System.IO;
using System.Text.RegularExpressions;

namespace LINQtoLDAPDemo_Setup.ExcelTestData
{
    public class EmployeeImporter
    {
        private LinqToExcel.ExcelQueryFactory _excel;
        public EmployeeImporter()
        {
            string projectPath = Regex.Split(AppDomain.CurrentDomain.BaseDirectory, @"[\d\w\s]+\\[\d\w\s]+\\$")[0]; // knock off "/bin/Debug/"
            string excelFilePath = System.IO.Path.Combine(projectPath, "ExcelTestData\\EmployeeData.xls");

            _excel = new ExcelQueryFactory(excelFilePath);

            SetExcelMapping();
        }

        private void SetExcelMapping()
        {
            _excel.AddMapping<ExcelEmployee>(x => x.EmployeeID, "EmployeeID");
            _excel.AddMapping<ExcelEmployee>(x => x.LastName, "LastName");
            _excel.AddMapping<ExcelEmployee>(x => x.FirstName, "FirstName");
            _excel.AddMapping<ExcelEmployee>(x => x.Title, "Title");
            _excel.AddMapping<ExcelEmployee>(x => x.TitleOfCourtesy, "TitleOfCourtesy");
            _excel.AddMapping<ExcelEmployee>(x => x.BirthDate, "BirthDate");
            _excel.AddMapping<ExcelEmployee>(x => x.HireDate, "HireDate");
            _excel.AddMapping<ExcelEmployee>(x => x.Address, "Address");
            _excel.AddMapping<ExcelEmployee>(x => x.City, "City");
            _excel.AddMapping<ExcelEmployee>(x => x.Region, "Region");
            _excel.AddMapping<ExcelEmployee>(x => x.PostalCode, "PostalCode");
            _excel.AddMapping<ExcelEmployee>(x => x.Country, "Country");
            _excel.AddMapping<ExcelEmployee>(x => x.HomePhone, "HomePhone");
            _excel.AddMapping<ExcelEmployee>(x => x.Extension, "Extension");
            _excel.AddMapping<ExcelEmployee>(x => x.Notes, "Notes");
            // LinqToExcel, why you no like nullable int?!
            _excel.AddMapping<ExcelEmployee>(x => x.ReportsTo, "ReportsTo");
            _excel.AddMapping<ExcelEmployee>(x => x.PhotoPath, "PhotoPath");
        }

        public Employee[] GetEmployeesFromExcel()
        {
            ExcelEmployee[] excelEmployees = _excel.Worksheet<ExcelEmployee>("Sheet1").ToArray<ExcelEmployee>();

            List<Employee> employees = new List<Employee>();
            excelEmployees.ToList().ForEach(x => employees.Add(x.ToEmployee()));

            return employees.ToArray();
        }
    }
}
